﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ICSharpCode.Data.Core.DatabaseObjects;
using System.Data;
using System.Data.Sql;
using System.Data.Common;
using System.Data.Odbc;
using ICSharpCode.Data.Core.Interfaces;
using System.Windows;
using System.Windows.Threading;
using ICSharpCode.Data.Core.Enums;

namespace ICSharpCode.Data.Core.DatabaseDrivers.Oracle
{
    public class OracleDatabaseDriver : DatabaseDriver<OracleDatasource>
    {
        #region Consts

        private const string _getTables = @"SELECT tname, tabtype FROM tab";

        private const string _getColumns = @"SELECT * FROM user_tab_cols WHERE table_name = '{0}'";

        private const string _getProcedures = @"select OBJECT_NAME from SYS.ALL_OBJECTS 
                                                where upper(OBJECT_TYPE) = 'PROCEDURE' and OWNER='{0}'
                                                order by OBJECT_NAME";

        private const string _getPackages = @"select OBJECT_NAME from SYS.ALL_OBJECTS 
                                              where upper(OBJECT_TYPE) = 'PACKAGE' and OWNER='{0}'
                                              order by OBJECT_NAME";

        private const string _getProceduresInPackage = @"select procedure_name from all_procedures
                                                         where owner = '{0}' and object_name = '{1}'
                                                         order by procedure_name";

        private const string _getParameters = @"select ARGUMENT_NAME, IN_OUT, DATA_TYPE, DATA_LENGTH from SYS.ALL_ARGUMENTS 
                                                where OWNER='{0}' and object_name='{1}'
                                                order by OBJECT_NAME, SEQUENCE";

        #endregion Consts

        public override string Name
        {
            get { return "Oracle ODBC"; }
        }

        public override void PopulateDatasources()
        {
            DatabaseObjectsCollection<OracleDatasource> datasources = new DatabaseObjectsCollection<OracleDatasource>(null);

            // get user dsn's
            Microsoft.Win32.RegistryKey reg = (Microsoft.Win32.Registry.CurrentUser).OpenSubKey("Software");
            if (reg != null)
            {
                reg = reg.OpenSubKey("ODBC");
                if (reg != null)
                {
                    reg = reg.OpenSubKey("ODBC.INI");
                    if (reg != null)
                    {
                        reg = reg.OpenSubKey("ODBC Data Sources");
                        if (reg != null)
                        {
                            // Get all Oracle DSN entries defined in DSN_LOC_IN_REGISTRY.
                            foreach (string sName in reg.GetValueNames())
                            {
                                if (reg.GetValue(sName).ToString().ToLower().Contains("oracle"))
                                {
                                    OracleDatasource datasource = new OracleDatasource(this) { Name = sName };
                                    datasources.Add(datasource);
                                }
                            }
                        }
                        try
                        {
                            reg.Close();
                        }
                        catch { /* ignore this exception if we couldn't close */ }
                    }
                }
            }

            Datasources = datasources;
        }

        public override void PopulateDatabases(IDatasource datasource)
        {
            DatabaseObjectsCollection<IDatabase> databases = new DatabaseObjectsCollection<IDatabase>(datasource);

            var odbcConnection = new OdbcConnection();
            odbcConnection.ConnectionString = datasource.ConnectionString;
            try
            {
                odbcConnection.Open();
            }
            catch (OdbcException ex)
            {
                switch (ex.ErrorCode)
                {
                    case 2:
                    case 3:
                    case 53:
                        Application.Current.Dispatcher.BeginInvoke(DispatcherPriority.Background, new Action(() =>
                        {
                            Datasources.Remove(datasource as OracleDatasource);
                        }));
                        break;
                    default:
                        break;
                }

                throw ex;
            }

            string sql = "select tablespace_name from user_tablespaces";

            OdbcCommand odbcCommand = new OdbcCommand(sql, odbcConnection);
            odbcCommand.CommandTimeout = 20;

            OdbcDataReader odbcDataReader = odbcCommand.ExecuteReader();

            while (odbcDataReader.Read())
            {
                string dbName = odbcDataReader["tablespace_name"].ToString();
                if (dbName.ToLower() != "indexes")
                    databases.Add(new Database(datasource) { Name = dbName });
            }

            odbcDataReader.Close();

            datasource.Databases = databases;

            if (odbcConnection != null && odbcConnection.State == ConnectionState.Open)
                odbcConnection.Close();
        }

        public override DatabaseObjectsCollection<ITable> LoadTables(IDatabase database)
        {
            DatabaseObjectsCollection<ITable> tables = new DatabaseObjectsCollection<ITable>(database);

            var odbcConnection = new OdbcConnection(database.Datasource.ConnectionString);
            odbcConnection.Open();
            odbcConnection.ChangeDatabase(database.Name);
            /*using (var da = new SqlDataAdapter(_getConstraintsScript, odbcConnection))
            {
                DataTable dtConstraints = new DataTable("Constraints");
                da.Fill(dtConstraints);

                for (int i = 0; i < dtConstraints.Rows.Count; i++)
                {
                    string constraintName = (string)dtConstraints.Rows[i]["ConstraintName"];

                    IConstraint constraint = database.Constraints.FirstOrDefault(c => c.Name == constraintName);

                    if (constraint == null)
                    {
                        constraint = new ICSharpCode.Data.Core.DatabaseObjects.Constraint();
                        constraint.Name = constraintName;
                        constraint.FKTableName = (string)dtConstraints.Rows[i]["FKTable"];
                        constraint.PKTableName = (string)dtConstraints.Rows[i]["PKTable"];

                        database.Constraints.Add(constraint);
                    }

                    constraint.FKColumnNames.Add((string)dtConstraints.Rows[i]["FKColumn"]);
                    constraint.PKColumnNames.Add((string)dtConstraints.Rows[i]["PKColumn"]);
                }
            }*/

            using (var da = new OdbcDataAdapter(_getTables, odbcConnection))
            {
                DataTable dtTables = new DataTable("Tables");
                da.Fill(dtTables);

                for (int i = 0; i < dtTables.Rows.Count; i++)
                {
                    string tableName = (string)dtTables.Rows[i]["tname"];
                    string tableType = (string)dtTables.Rows[i]["tabtype"];

                    if (tableType.ToLower() == "table")
                    {
                        Table table = new Table() { SchemaName = database.Name, TableName = tableName };
                        LoadColumns(odbcConnection, table, TableType.Table);

                        //table.Constraints = database.Constraints.Where(constraint => constraint.FKTableName == tableName).ToDatabaseObjectsCollection(table);
                        tables.Add(table);
                    }
                }
            }

            odbcConnection.Close();

            return tables;
        }

        public override DatabaseObjectsCollection<IView> LoadViews(IDatabase database)
        {
            DatabaseObjectsCollection<IView> views = new DatabaseObjectsCollection<IView>(database);

            var odbcConnection = new OdbcConnection(database.ConnectionString);
            odbcConnection.Open();
            odbcConnection.ChangeDatabase(database.Name);
            
            using (var da = new OdbcDataAdapter(_getTables, odbcConnection))
            {
                DataTable dtViews = new DataTable("Views");
                da.Fill(dtViews);

                for (int i = 0; i < dtViews.Rows.Count; i++)
                {
                    string tableName = (string)dtViews.Rows[i]["tname"];
                    string tableType = (string)dtViews.Rows[i]["tabtype"];

                    if (tableType.ToLower() == "view")
                    {
                        //View view = new View() { SchemaName = schemaName, TableName = viewName, Query = LoadViewQuery(sqlConnection, schemaName, viewName) };            
                        View view = new View() { SchemaName = database.Name, TableName = tableName };
                        LoadColumns(odbcConnection, view, TableType.View);

                        views.Add(view);
                    }
                }
            }

            odbcConnection.Close();

            return views;
        }

        private void LoadColumns(OdbcConnection odbcConnection, ITable table, TableType tableType)
        {
            using (var dataAdapter =
                new OdbcDataAdapter(string.Format(_getColumns, table.TableName), odbcConnection))
            {
                DataTable dtColumns = new DataTable("Columns");
                dataAdapter.Fill(dtColumns);

                for (int j = 0; j < dtColumns.Rows.Count; j++)
                {
                    Column column = new Column(table);
                    column.ColumnId = Convert.ToInt32(dtColumns.Rows[j]["Column_ID"]);
                    column.Name = (string)dtColumns.Rows[j]["Column_Name"];
                    column.DataType = (string)dtColumns.Rows[j]["Data_Type"];
                    column.SystemType = (string)dtColumns.Rows[j]["Data_Type"];
                    column.Length = Convert.ToInt32(dtColumns.Rows[j]["Data_Length"]);

                    if (column.Length == -1)
                    {
                        switch (column.DataType.ToLower())
                        {
                            case "varchar2":
                            case "nvarchar2":
                                column.DataType += "(max)";
                                break;
                            default:
                                break;
                        }

                        switch (column.SystemType.ToLower())
                        {
                            case "varchar2":
                            case "nvarchar2":
                                column.SystemType += "(max)";
                                break;
                            default:
                                break;
                        }
                    }

                    if (!DBNull.Value.Equals(dtColumns.Rows[j]["Data_Precision"]))
                        column.Precision = Convert.ToInt32(dtColumns.Rows[j]["Data_Precision"]);
                    if (!DBNull.Value.Equals(dtColumns.Rows[j]["Data_Scale"]))
                        column.Scale = Convert.ToInt32(dtColumns.Rows[j]["Data_Scale"]);
                    // @TODO: column.IsIdentity = (bool)dtColumns.Rows[j]["IsIdentity"];
                    column.IsNullable = (string)(dtColumns.Rows[j]["Nullable"]) == "Y";
                    // @TODO: column.IsPrimaryKey = (bool)dtColumns.Rows[j]["IsPrimaryKey"];

                    table.Items.Add(column);
                }
            }
        }

        public override DatabaseObjectsCollection<IProcedure> LoadProcedures(IDatabase database)
        {
            DatabaseObjectsCollection<IProcedure> procedures = new DatabaseObjectsCollection<IProcedure>(database.Datasource);

            var odbcConnection = new OdbcConnection(database.ConnectionString);
            odbcConnection.Open();
            string dbSchema = database.Datasource.GetConnectionStringSetting("UID");

            // load packages
            using (var da = new OdbcDataAdapter(String.Format(_getPackages, dbSchema), odbcConnection))
            {
                DataTable dtPackages = new DataTable("Packages");
                da.Fill(dtPackages);

                for (int i = 0; i < dtPackages.Rows.Count; i++)
                {
                    string packageName = (string)dtPackages.Rows[i]["OBJECT_NAME"];

                    da.SelectCommand = new OdbcCommand(string.Format(_getProceduresInPackage, dbSchema, packageName), odbcConnection);
                    DataTable dtProcedures = new DataTable("Procedures");
                    da.Fill(dtProcedures);

                    for (int j = 0; j < dtProcedures.Rows.Count; j++)
                    {
                        Procedure procedure = new Procedure();
                        procedure.Name = (string)dtProcedures.Rows[j]["PROCEDURE_NAME"];
                        procedure.SchemaName = dbSchema;
                        procedure.ProcedureType = ProcedureType.SQL;

                        procedures.Add(procedure);
                    }
                }
            }

            // load store procedures
            using (var da = new OdbcDataAdapter(String.Format(_getProcedures, dbSchema), odbcConnection))
            {
                DataTable dtProcedures = new DataTable("Procedures");
                da.Fill(dtProcedures);

                for (int i = 0; i < dtProcedures.Rows.Count; i++)
                {
                    Procedure procedure = new Procedure();
                    procedure.Name = (string)dtProcedures.Rows[i]["OBJECT_NAME"];
                    procedure.SchemaName = dbSchema;
                    procedure.ProcedureType = ProcedureType.SQL;
                    
                    procedures.Add(procedure);
                }
            }

            // load parameters
            using (var da = new OdbcDataAdapter())
            {
                foreach (Procedure procedure in procedures)
                {
                    procedure.Items = new DatabaseObjectsCollection<IProcedureParameter>(procedure);

                    DatabaseObjectsCollection<IProcedureParameter> procedureParameters = new DatabaseObjectsCollection<IProcedureParameter>(procedure);

                    da.SelectCommand = new OdbcCommand(string.Format(_getParameters, dbSchema, procedure.Name), odbcConnection);
                    DataTable dtProcedureParameters = new DataTable("ProcedureParameters");
                    da.Fill(dtProcedureParameters);

                    for (int j = 0; j < dtProcedureParameters.Rows.Count; j++)
                    {
                        ProcedureParameter procedureParameter = new ProcedureParameter();
                        procedureParameter.Name = (string)dtProcedureParameters.Rows[j]["ARGUMENT_NAME"];
                        if (procedureParameter.Name.StartsWith("@"))
                            procedureParameter.Name = procedureParameter.Name.Substring(1);

                        if (dtProcedureParameters.Rows[j]["DATA_TYPE"] != DBNull.Value)
                            procedureParameter.DataType = (string)dtProcedureParameters.Rows[j]["DATA_TYPE"];

                        if (dtProcedureParameters.Rows[j]["DATA_LENGTH"] != DBNull.Value)
                            procedureParameter.Length = Convert.ToInt32(dtProcedureParameters.Rows[j]["DATA_LENGTH"]);

                        string parameterMode = (string)dtProcedureParameters.Rows[j]["IN_OUT"];
                        if (parameterMode == "IN")
                            procedureParameter.ParameterMode = ParameterMode.In;
                        else if (parameterMode == "OUT")
                            procedureParameter.ParameterMode = ParameterMode.Out;
                        else
                            procedureParameter.ParameterMode = ParameterMode.InOut;

                        procedure.Items.Add(procedureParameter);
                    }
                }
            }

            odbcConnection.Close();

            return procedures;
        }
    }
}
